package batch.pmgm;

import java.sql.*;

import java.util.Date;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.*;

import batch.common.db.*;
import batch.common.report.*;

public class PmgmSt_mb {
    private final String TODAY  = DateFormatUtils.format(new Date(), "yyyyMMdd");
    
    private final String MAILSUBJECT = "PmgmSt_mb Error! (" + TODAY + ")"; 

    private String batch_date;

    /*******************************************************************************************************/
    /* Name : PmgmSt_mb
    /* Arguments : none
    /* Return : none
    /* Description : creator
    /*******************************************************************************************************/
    public PmgmSt_mb()
    {
        batch_date = TODAY;
    }

    /*******************************************************************************************************/
    /* Name : PmgmSt_mb
    /* Arguments : String(--yyyymmdd--)
    /* Return : none
    /* Description : creator
    /*******************************************************************************************************/
    public PmgmSt_mb(String date)
    {
        batch_date = date;
    }
    
    /*******************************************************************************************************/
    /* Name : main
    /* Arguments : String(--yyyymmdd--)
    /*******************************************************************************************************/
    public static void main(String args[])
    {
        PmgmSt_mb pmgm_mb = null;

        if ( args==null || args.length==0 ) {
            pmgm_mb = new PmgmSt_mb();
        } else {
            pmgm_mb = new PmgmSt_mb(args[0]);
        }

        Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) Start.");
        pmgm_mb.start();
        Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) End.");
    }
    /*******************************************************************************************************/

    /*******************************************************************************************************/
    /* Name : start
    /* Arguments : none
    /* Return : none
    /* Description : execute each statistics function
    /*******************************************************************************************************/
    public void start()
    {
        Connection conn = null;
        Statement stmt = null;
        int nCnt = 0;

        try
        {
            // get connection
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            //insert into G_PMGM_ST_MB
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) member statistics start.");
            nCnt = insertPmgm_mb(stmt);
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) success. " + nCnt + " inserted.");

            //insert into G_PMGM_ST_AGE
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) age statistics start.");
            nCnt = insertPmgm_age(stmt);
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) success. " + nCnt + " inserted.");

            //insert into G_PMGM_ST_BRAND
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) brand statistics start.");
            nCnt = insertPmgm_brand(stmt);
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) success. " + nCnt + " inserted.");

            //insert into G_PMGM_ST_GENDER
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) gender statistics start.");
            nCnt = insertPmgm_gender(stmt);
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) success. " + nCnt + " inserted.");

            //insert into G_PMGM_ST_GRADE
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) grade statistics start.");
            nCnt = insertPmgm_grade(stmt);
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) success. " + nCnt + " inserted.");

            //insert into G_PMGM_ST_OPT
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) opt-in statistics start.");
            nCnt = insertPmgm_opt(stmt);
            Logger.getLogger("pmgm.pmgmst_mb").info(" ( * ) success. " + nCnt + " inserted.");

            // final commit
            conn.commit();
        } catch(Exception e) {
            if (conn != null) try { conn.rollback(); } catch (Exception ex) { ex.printStackTrace(); }
            
            e.printStackTrace();

            Logger.getLogger("pmgm.pmgmst_mb").info(" ( ! ) " + e.toString());

            StackTraceElement[] stElement = e.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("pmgm.pmgmst_mb").info(" ( ! ) " + stElement[p].toString());
            }

            ReportError.byEmail(MAILSUBJECT, e.toString());
        } finally {
            if (stmt != null) try { stmt.close(); } catch (Exception e) { e.printStackTrace(); }
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }

    /*******************************************************************************************************/
    /* Name : insertPmgm_mb
    /* Arguments : Statement
    /* Return : int
    /* Description : G_PMGM_ST_MB
    /*******************************************************************************************************/
    public int insertPmgm_mb(Statement stmt) throws Exception
    {
        StringBuffer sql = new StringBuffer();
        sql.append(" INSERT INTO G_PMGM_ST_MB \n");
        sql.append("    SELECT SEQ_G_PMGM_ST_MB.NEXTVAL, A.* \n");
        sql.append("       FROM ( \n");
        sql.append("            SELECT  B.MST_CD, B.SITE_CD, B.SUB_CD, '" + batch_date + "', COUNT(*) CNT \n");
        sql.append("               FROM   G_MB_MEMBER A, G_PMGM_MST_SUB B \n");
        sql.append("             WHERE  A.MEMBERGBN =  B.MST_CD || B.SUB_CD AND \n");
        sql.append("                           A.CREATED   >= TO_DATE('" + batch_date + "','YYYYMMDD') AND \n");
        sql.append("                           A.CREATED   <  TO_DATE('" + batch_date + "','YYYYMMDD') + 1 AND \n");
        sql.append("                           A.REG_COUNTRY = B.SITE_CD \n");
        sql.append("              GROUP BY B.MST_CD, B.SITE_CD, B.SUB_CD ) A ");

        return stmt.executeUpdate(sql.toString());
    }

    /*******************************************************************************************************/
    /* Name : insertPmgm_age
    /* Arguments : Statement
    /* Return : int
    /* Description : G_PMGM_ST_AGE
    /*******************************************************************************************************/
    public int insertPmgm_age(Statement stmt) throws Exception
    {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO G_PMGM_ST_AGE \n");
        sql.append("    SELECT SEQ_G_PMGM_ST_MB.NEXTVAL, A.* \n");
        sql.append("    FROM ( \n");
        sql.append("        SELECT  B.MST_CD, B.SITE_CD, GET_PMGM_AGE('" + batch_date + "', BIRTH), '" + batch_date + "', COUNT(*) CNT \n");
        sql.append("          FROM   G_MB_MEMBER A, G_PMGM_MST_SUB B \n");
        sql.append("          WHERE  A.MEMBERGBN = B.MST_CD || B.SUB_CD AND \n");
        sql.append("                A.CREATED   >= TO_DATE('" + batch_date + "','YYYYMMDD') AND \n");
        sql.append("                A.CREATED   <  TO_DATE('" + batch_date + "','YYYYMMDD') + 1 AND \n");
        sql.append("                A.REG_COUNTRY = B.SITE_CD \n");
        sql.append("          GROUP BY B.MST_CD, B.SITE_CD, GET_PMGM_AGE('" + batch_date + "', BIRTH) ) A ");

        return stmt.executeUpdate(sql.toString());
    }

    /*******************************************************************************************************/
    /* Name : insertPmgm_brand
    /* Arguments : Statement
    /* Return : int
    /* Description : G_PMGM_ST_BRAND
    /*******************************************************************************************************/
    public int insertPmgm_brand(Statement stmt) throws Exception
    {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO G_PMGM_ST_BRAND \n");
        sql.append("    SELECT SEQ_G_PMGM_ST_MB.NEXTVAL, A.* \n");
        sql.append("    FROM ( \n");
        sql.append("        SELECT  B.MST_CD, B.SITE_CD, A.HP_BRAND, '" + batch_date + "', COUNT(*) CNT \n");
        sql.append("          FROM   G_MB_MEMBER A, G_PMGM_MST_SUB B \n");
        sql.append("          WHERE  A.MEMBERGBN = B.MST_CD || B.SUB_CD AND \n");
        sql.append("                A.CREATED   >= TO_DATE('" + batch_date + "','YYYYMMDD') AND \n");
        sql.append("                A.CREATED   <  TO_DATE('" + batch_date + "','YYYYMMDD') + 1 AND \n");
        sql.append("                A.REG_COUNTRY = B.SITE_CD \n");
        sql.append("          GROUP BY B.MST_CD, B.SITE_CD, A.HP_BRAND ) A ");

        return stmt.executeUpdate(sql.toString());
    }

    /*******************************************************************************************************/
    /* Name : insertPmgm_gender
    /* Arguments : Statement
    /* Return : int
    /* Description : G_PMGM_ST_GENDER
    /*******************************************************************************************************/
    public int insertPmgm_gender(Statement stmt) throws Exception
    {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO G_PMGM_ST_GENDER \n");
        sql.append("    SELECT SEQ_G_PMGM_ST_MB.NEXTVAL, A.* \n");
        sql.append("    FROM ( \n");
        sql.append("        SELECT  B.MST_CD, B.SITE_CD, DECODE(A.GENDER,'F',A.GENDER,'M',A.GENDER,'N') GENDER, '" + batch_date + "', COUNT(*) CNT \n");
        sql.append("          FROM   G_MB_MEMBER A, G_PMGM_MST_SUB B \n");
        sql.append("          WHERE  A.MEMBERGBN = B.MST_CD || B.SUB_CD AND \n");
        sql.append("                A.CREATED   >= TO_DATE('" + batch_date + "','YYYYMMDD') AND \n");
        sql.append("                A.CREATED   <  TO_DATE('" + batch_date + "','YYYYMMDD') + 1 AND \n");
        sql.append("                A.REG_COUNTRY = B.SITE_CD \n");
        sql.append("          GROUP BY B.MST_CD, B.SITE_CD, DECODE(A.GENDER,'F',A.GENDER,'M',A.GENDER,'N') ) A ");

        return stmt.executeUpdate(sql.toString());
    }

    /*******************************************************************************************************/
    /* Name : insertPmgm_grade
    /* Arguments : Statement
    /* Return : int
    /* Description : G_PMGM_ST_GRADE
    /*******************************************************************************************************/
    public int insertPmgm_grade(Statement stmt) throws Exception
    {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO G_PMGM_ST_GRADE \n");
        sql.append("    SELECT SEQ_G_PMGM_ST_MB.NEXTVAL, A.* \n");
        sql.append("    FROM ( \n");
        sql.append("        SELECT  B.MST_CD, B.SITE_CD, A.GRADE, '" + batch_date + "', COUNT(*) CNT \n");
        sql.append("          FROM   G_MB_MEMBER A, G_PMGM_MST_SUB B \n");
        sql.append("          WHERE  A.MEMBERGBN = B.MST_CD || B.SUB_CD AND \n");
        sql.append("                A.CREATED   >= TO_DATE('" + batch_date + "','YYYYMMDD') AND \n");
        sql.append("                A.CREATED   <  TO_DATE('" + batch_date + "','YYYYMMDD') + 1 AND \n");
        sql.append("                A.REG_COUNTRY = B.SITE_CD \n");
        sql.append("          GROUP BY B.MST_CD, B.SITE_CD, A.GRADE ) A ");

        return stmt.executeUpdate(sql.toString());
    }

    /*******************************************************************************************************/
    /* Name : insertPmgm_opt
    /* Arguments : Statement
    /* Return : int
    /* Description : G_PMGM_ST_OPT
    /*******************************************************************************************************/
    public int insertPmgm_opt(Statement stmt) throws Exception
    {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO G_PMGM_ST_OPT \n");
        sql.append("    SELECT SEQ_G_PMGM_ST_MB.NEXTVAL, A.* \n");
        sql.append("    FROM ( \n");
        sql.append("        SELECT  B.MST_CD, B.SITE_CD, A.NEWS_LETTER, '" + batch_date + "', COUNT(*) CNT \n");
        sql.append("          FROM   G_MB_MEMBER A, G_PMGM_MST_SUB B \n");
        sql.append("          WHERE  A.MEMBERGBN = B.MST_CD || B.SUB_CD AND \n");
        sql.append("                A.CREATED   >= TO_DATE('" + batch_date + "','YYYYMMDD') AND \n");
        sql.append("                A.CREATED   <  TO_DATE('" + batch_date + "','YYYYMMDD') + 1 AND \n");
        sql.append("                A.REG_COUNTRY = B.SITE_CD \n");
        sql.append("          GROUP BY B.MST_CD, B.SITE_CD, A.NEWS_LETTER ) A ");

        return stmt.executeUpdate(sql.toString());
    }
}
